package csc.fresher.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import csc.fresher.dao.InterestRateDao;
import csc.fresher.entity.InterestRate;
import csc.fresher.entity.SavingAccountHasInterestRate;
import csc.fresher.entity.SavingInterestRate;
import csc.fresher.utils.DBUtils;
import csc.fresher.utils.DatabaseUtil;

@Repository
public class SavingInterestRateDaoImpl implements InterestRateDao {

	private static final Logger logger = Logger
			.getLogger(PeriodTypeDaoImpl.class);

	@Autowired
	DataSource dataSource;

	@Override
	public void insert(InterestRate interestRate) {
		// TODO Auto-generated method stub
		String query = "INSERT INTO `SavingInterestRate`(`SavingInterestRateID`,`createdDate`,`Rate`, `CurrencyID`, `PeriodTypeID`,`FromDate`,`ToDate`) VALUES (?,?,?,?,?,?,?)";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			DatabaseUtil.executeUpdate(
					preparedStatement,
					connection,
					query,
					new Object[] { interestRate.getInterestRateID(),
							interestRate.getCreatedDate(),
							interestRate.getRate(),
							interestRate.getCurrencyID(),
							interestRate.getPeriodTypeID(),
							interestRate.getFromDate(),
							interestRate.getToDate() });
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeConnection(connection, preparedStatement, logger);
		}
	}

	// not process
	@Override
	public void update(InterestRate interestRate) {
		// TODO Auto-generated method stub
		String query = "UPDATE `SavingInterestRate` SET `SavingInterestRate`.`Rate` = ?, SET `SavingInterestRate`.`CurrencyID`=?, SET `SavingInterestRate`.`PeriodTypeID`=?, SET `SavingInterestRate`.`FromDate`=?, SET `SavingInterestRate`.`ToDate`=? WHERE `SavingInterestRate`.`SavingInterestRateID` = ?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			DatabaseUtil.executeUpdate(
					preparedStatement,
					connection,
					query,
					new Object[] { interestRate.getRate(),
							interestRate.getCurrencyID(),
							interestRate.getPeriodTypeID(),
							interestRate.getFromDate(),
							interestRate.getToDate(),
							interestRate.getInterestRateID() });
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeConnection(connection, preparedStatement, logger);
		}
	}

	@Override
	public InterestRate getInterestRateByID(int interestRateID) {
		InterestRate interestRate = null;
		String query = "SELECT * FROM `SavingInterestRate` WHERE `SavingInterestRate`.`SavingInterestRateID`=?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, new Object[] { interestRateID });
			interestRate = new SavingInterestRate();
			if (rs.next()) {
				interestRate.setCreatedDate(rs.getDate("createdDate"));
				interestRate.setCurrencyID(rs.getInt("CurrencyID"));
				interestRate.setFromDate(rs.getDate("FromDate"));
				interestRate.setInterestRateID(rs
						.getInt("SavingInterestRateID"));
				interestRate.setPeriodTypeID(rs.getInt("PeriodTypeID"));
				interestRate.setRate(rs.getDouble("Rate"));
				interestRate.setToDate(rs.getDate("ToDate"));
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeResultSetConnection(connection,
					preparedStatement, rs, logger);
		}
		return interestRate;
	}

	// function get
	/*
	 * private SavingInterestRate getSavingInterestResultSet(ResultSet rs)
	 * throws SQLException{ SavingInterestRate savingInterestRate = new
	 * SavingInterestRate();
	 * savingInterestRate.setInterestRateID(rs.getInt("SavingInterestRateID"));
	 * savingInterestRate.setCreatedDate(rs.getDate("createdDate"));
	 * savingInterestRate.setRate(rs.getDouble("Rate"));
	 * savingInterestRate.setCurrencyID(rs.getInt("Currency_CurrencyID"));
	 * savingInterestRate.setPeriodTypeID(rs.getInt("PeriodType_PeriodTypeID"));
	 * savingInterestRate.setFromDate(rs.getDate("FromDate"));
	 * savingInterestRate.setFromDate(rs.getDate("ToDate")); return
	 * savingInterestRate; }
	 */
	@Override
	public InterestRate getInterestRateByType(int currencyID, int periodTypeID) {
		InterestRate interestRate = null;
		String query = "SELECT * FROM `SavingInterestRate` WHERE `SavingInterestRate`.`CurrencyID`=? AND `SavingInterestRate`.`PeriodTypeID`=?"
				+ " AND `FromDate` <= ? ORDER BY `createdDate` DESC LIMIT 1 ";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			Timestamp timestamp = new Timestamp(System.currentTimeMillis());
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, new Object[] { currencyID, periodTypeID, timestamp });
			interestRate = new SavingInterestRate();
			if (rs.next()) {
				interestRate.setCreatedDate(rs.getDate("createdDate"));
				interestRate.setCurrencyID(rs.getInt("CurrencyID"));
				interestRate.setFromDate(rs.getDate("FromDate"));
				interestRate.setInterestRateID(rs
						.getInt("SavingInterestRateID"));
				interestRate.setPeriodTypeID(rs.getInt("PeriodTypeID"));
				interestRate.setRate(rs.getDouble("Rate"));
				interestRate.setToDate(rs.getDate("ToDate"));
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeResultSetConnection(connection,
					preparedStatement, rs, logger);
		}
		return interestRate;

	}

	@Override
	public InterestRate getInterestNewestRateByType(int currencyID,
			int periodTypeID) {
		InterestRate interestRate = null;
		String query = "SELECT * FROM `SavingInterestRate` WHERE `SavingInterestRate`.`CurrencyID`=? AND `SavingInterestRate`.`PeriodTypeID`=? ORDER BY `SavingInterestRate`.`createdDate` DESC LIMIT 1";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, new Object[] { currencyID, periodTypeID });
			interestRate = new SavingInterestRate();
			if (rs.next()) {
				interestRate.setCreatedDate(rs.getDate("createdDate"));
				interestRate.setCurrencyID(rs.getInt("CurrencyID"));
				interestRate.setFromDate(rs.getDate("FromDate"));
				interestRate.setInterestRateID(rs
						.getInt("SavingInterestRateID"));
				interestRate.setPeriodTypeID(rs.getInt("PeriodTypeID"));
				interestRate.setRate(rs.getDouble("Rate"));
				interestRate.setToDate(rs.getDate("ToDate"));
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeResultSetConnection(connection,
					preparedStatement, rs, logger);
		}
		return interestRate;

	}

	@Override
	public List<InterestRate> getAllInterestRates() {
		List<InterestRate> interestRates = null;
		InterestRate interestRate = null;
		String query = "SELECT * FROM `SavingInterestRate`";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, null);
			interestRates = new ArrayList<>();
			while (rs.next()) {
				interestRate = new SavingInterestRate();
				interestRate.setCreatedDate(rs.getDate("createdDate"));
				interestRate.setCurrencyID(rs.getInt("CurrencyID"));
				interestRate.setFromDate(rs.getDate("FromDate"));
				interestRate.setInterestRateID(rs
						.getInt("SavingInterestRateID"));
				interestRate.setPeriodTypeID(rs.getInt("PeriodTypeID"));
				interestRate.setRate(rs.getDouble("Rate"));
				interestRate.setToDate(rs.getDate("ToDate"));
				interestRates.add(interestRate);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeResultSetConnection(connection,
					preparedStatement, rs, logger);
		}
		return interestRates;
	}

	@Override
	public List<InterestRate> getListInterestRates(int startIndex, int maxReturn) {
		List<InterestRate> interestRates = null;
		InterestRate interestRate = null;
		String query = "SELECT * FROM `SavingInterestRate` LIMIT ?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, null);
			interestRates = new ArrayList<>();
			while (rs.next()) {
				interestRate = new SavingInterestRate();
				interestRate.setCreatedDate(rs.getDate("createdDate"));
				interestRate.setCurrencyID(rs.getInt("CurrencyID"));
				interestRate.setFromDate(rs.getDate("FromDate"));
				interestRate.setInterestRateID(rs
						.getInt("SavingInterestRateID"));
				interestRate.setPeriodTypeID(rs.getInt("PeriodTypeID"));
				interestRate.setRate(rs.getFloat("Rate"));
				interestRate.setToDate(rs.getDate("ToDate"));
				interestRates.add(interestRate);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeResultSetConnection(connection,
					preparedStatement, rs, logger);
		}
		return interestRates;
	}

	@Override
	public int totalInterestRates() {
		// TODO Auto-generated method stub
		return getAllInterestRates().size();
	}

	// @Override
	public List<InterestRate> getAllInterestRateByCurrencyID(int currencyID) {
		List<InterestRate> interestRates = null;
		InterestRate interestRate = null;
		String query = "SELECT * FROM `SavingInterestRate` WHERE `SavingInterestRate`.`CurrencyID`=?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, new Object[] { currencyID });
			interestRates = new ArrayList<>();
			while (rs.next()) {
				interestRate = new SavingInterestRate();
				interestRate.setCreatedDate(rs.getDate("createdDate"));
				interestRate.setCurrencyID(rs.getInt("CurrencyID"));
				interestRate.setFromDate(rs.getDate("FromDate"));
				interestRate.setInterestRateID(rs
						.getInt("SavingInterestRateID"));
				interestRate.setPeriodTypeID(rs.getInt("PeriodTypeID"));
				interestRate.setRate(rs.getFloat("Rate"));
				interestRate.setToDate(rs.getDate("ToDate"));
				interestRates.add(interestRate);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeResultSetConnection(connection,
					preparedStatement, rs, logger);
		}
		return interestRates;
	}

}
